package com.ultrawise.survey.module.surevybase.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.xml.rpc.ServiceException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.druid.sql.ast.statement.SQLWithSubqueryClause.Entry;
import com.ultrawise.survey.common.printfile.TypeToHtml;
import com.ultrawise.survey.contants.SurveyConstants;
import com.ultrawise.survey.core.Page;
import com.ultrawise.survey.core.base.BaseService;
import com.ultrawise.survey.core.base.DaoException;
import com.ultrawise.survey.module.surevybase.dao.TSurveyInfoObjtableDAO;
import com.ultrawise.survey.module.surevybase.vo.TSurveyInfoObjtable;
import com.ultrawise.survey.module.surevybase.vo.TSurveyInfoQuestion;

@Service("tSurveyInfoObjtableService")
public class TSurveyInfoObjtableService extends BaseService {
	@Autowired
	private TSurveyInfoObjtableDAO tSurveyInfoObjtableDAO;
	
	Map col = SurveyConstants.OBJECT_COLNUM_MYSQLDB;

	public Map<String, Object> getSurveyAll(Integer syid) {
		TSurveyInfoObjtable bInfoObjtable = (TSurveyInfoObjtable) tSurveyInfoObjtableDAO
				.findByID(syid, new TSurveyInfoObjtable(),
						TSurveyInfoObjtable.class);
		List<TSurveyInfoQuestion> lsquestList = (List<TSurveyInfoQuestion>) tSurveyInfoObjtableDAO
				.findByColunm("sy_id", syid.toString(),
						new TSurveyInfoQuestion(), TSurveyInfoQuestion.class);
		String html = "";
		for (int i = 0; lsquestList != null && i < lsquestList.size(); i++) {
			TSurveyInfoQuestion tq = lsquestList.get(i);
			tq.setQn_name(tq.getQn_name() + "<br/>");
			tq.setQn_options(TypeToHtml.getHtmlByType(tq.getQn_type(),
					tq.getQn_options(), tq.getId()));

		}
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("base", bInfoObjtable);
		map.put("question", lsquestList);
		return map;
	}

	/****
	 * 返回全部问卷类表信息，根据翻页信息
	 * 
	 * @param start
	 * @param limel
	 * @param objinfo
	 * @return
	 * @throws ServiceException
	 */

	public Page<TSurveyInfoObjtable> getlistpage1(int start, int limel,
			TSurveyInfoObjtable tobj) throws ServiceException {
		try {
			Page<TSurveyInfoObjtable> page1 = null;
			page1 = tSurveyInfoObjtableDAO.getlistpage1(start, limel, tobj);
			return page1;
		} catch (Exception e) {
			throw new ServiceException("查询失败", e);
		}
	}

	public boolean createTableBySyid(Integer syid) {
		
		String createsql = "CREATE TABLE T_SURVEY_OBJ" + syid + "(";

		//createsql += " /`ID/` int(20) NOT NULL AUTO_INCREMENT ,";
		createsql += " `ID` int(20) NOT NULL AUTO_INCREMENT ,";
		createsql += " `CREATETIME` varchar(23) NULL ,";
		createsql += " `OBJIP` varchar(20) NULL ,";
		//createsql += " `AGE` int(3) NULL ,";
		List<TSurveyInfoObjtable> lst = (List<TSurveyInfoObjtable>) tSurveyInfoObjtableDAO
				.findByColunm("sy_id", syid.toString(),
						new TSurveyInfoObjtable(), TSurveyInfoObjtable.class);
		for (int i = 0; lst != null && i < lst.size(); i++) {
			TSurveyInfoObjtable tp = lst.get(i);
			//createsql += "/`" + tp.getOb_code() + "/` "
			createsql +=  "`"+tp.getOb_code() +"` "	
					+ col.get(tp.getOb_type()) + " "
					+ getLength(tp.getOb_type(), tp.getOb_size()) + "  "
					+ getDefault(tp.getOb_type(), tp.getOb_default()) + ", ";
		}
		

		createsql += " PRIMARY KEY (`ID`) )";

		createsql=createsql.replaceAll("/", "");
				
		try {
			tSurveyInfoObjtableDAO.execute(createsql);

		} catch (Exception e) {
			return false;
		}
		return true;
	}
	
	/**
	 * 1查询表是否存在
	 * @param syid
	 */
	public void publishTableObjBysyid(Integer syid){
		List<Map<String,Object>> lsts=tSurveyInfoObjtableDAO.queryBysql("select * from T_SURVEY_OBJ" + syid + " limit 1 ");
		String sql = "";
		if(lsts!=null && lsts.size()>0){
//			System.out.println("12311111");
			Map<String,Object> map = lsts.get(0);
			List<TSurveyInfoObjtable> lst = (List<TSurveyInfoObjtable>) tSurveyInfoObjtableDAO
					.findByColunm("sy_id", syid.toString(),new TSurveyInfoObjtable(), TSurveyInfoObjtable.class);
			
			try{
				map=notHasColumn(map,lst);
			for(Map.Entry<String, Object> emap:map.entrySet()){
				TSurveyInfoObjtable objcol =  (TSurveyInfoObjtable) emap.getValue();
				if(objcol!=null && objcol.getId()!=null &&!(emap.getKey().startsWith("##")) ){
					sql="alter table t_survey_obj"+syid+" add column `"+objcol.getOb_code()+"` "+ col.get(objcol.getOb_type()) + " "
						+ getLength(objcol.getOb_type(), objcol.getOb_size()) + "  "
						+ getDefault(objcol.getOb_type(), objcol.getOb_default()) + "; "; 
				}else{
					sql="ALTER TABLE `t_survey_obj"+syid+"` MODIFY COLUMN `"+objcol.getOb_code()+"` "+ col.get(objcol.getOb_type()) + " "
						+ getLength(objcol.getOb_type(), objcol.getOb_size()) + "  "
						+ getDefault(objcol.getOb_type(), objcol.getOb_default()) + "; "; 
				}
				tSurveyInfoObjtableDAO.execute(sql);
			}
			}catch(Exception e){
				map.clear();
				e.printStackTrace();
			}
			
		}else{
			//删除表，然后再进行创建
			tSurveyInfoObjtableDAO.execute("drop table T_SURVEY_OBJ" + syid);
			createTableBySyid(syid);
		}
		
	}

	/***
	 * 
	 * @param obtype
	 * @param ob_size
	 * @return
	 */
	private String getLength(String obtype, Integer ob_size) {
		Map<String, String> colength = SurveyConstants.OBJECT_COLNUM_LENGTH_MYSQLDB;
		if ("3".equals(obtype) || "5".equals(obtype) || "6".equals(obtype)) {
			return "(" + colength.get(obtype) + ")";
		} else {
			ob_size=(ob_size!=null)?ob_size:0;
			//设计极限值信息
			if ("2".equals(obtype) && ob_size > 254) {
				return "(200)";
			}
			if ("1".equals(obtype) && ob_size > 21485)
				return "(20000)";
			if ("4".equals(obtype))
				return "";
			return (ob_size != null) ? "(" + ob_size.toString() + ")" : "(50)";
		}
	}

	private String getDefault(String obtype, String defaultstr) {
		if (defaultstr != null && defaultstr.length() > 0) {
			if ("1".equals(obtype))
				return " DEFAULT '" + defaultstr + "' ";
			else if ("2".equals(obtype))
				return " DEFAULT " + defaultstr + " ";
			else if ("4".equals(obtype))
				return "";
			else
				return " DEFAULT NULL ";
		} else
			return " DEFAULT NULL ";

	}
	
	
	/*String str ="<script>function check(){alert('-------------');}</script>"
			request.setAttribute("str",str);*/
	
	
	
	public Integer addobjtable(Map<String,String> mapinfo){
		Integer ssyid=Integer.parseInt(mapinfo.get("syid"));
		String insql = "insert into t_survey_obj"+mapinfo.get("syid")+"(";
		String name="",vale="";
		for (Map.Entry<String, String> info : mapinfo.entrySet()){
			if("syid".equalsIgnoreCase(info.getKey()))
				continue;
			name += info.getKey()+",";
			vale +="'"+info.getValue()+"',";
		}
		insql+=name.substring(0,name.length()-1)+") values ("+vale.substring(0, vale.length()-1)+")";
		try {
			tSurveyInfoObjtableDAO.execute(insql);
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		return getlastid();
	}
	
	
	public Integer getlastid() {
		return tSurveyInfoObjtableDAO.getJdbcTemplate().queryForInt( 
				  "select LAST_INSERT_ID() AS id"); 
	}

	public boolean isExitTable(Integer syid) {
		return tSurveyInfoObjtableDAO.isExitTable(syid);
	}	
	
	
	/****
	 * 判断字段是否存在list对象中
	 * @param col
	 * @param lst
	 * @return
	 */
	private Map<String,Object> notHasColumn(Map<String,Object> map,List<TSurveyInfoObjtable> lst) throws Exception{
		Map<String,Object> newmap = new HashMap<String,Object>();
		for(TSurveyInfoObjtable objcol:lst){
			if(map.containsKey(objcol.getOb_code()))
				newmap.put("##"+objcol.getOb_code(),objcol);
			else
				newmap.put(objcol.getOb_code(), objcol);
		}
		
//		for(Map.Entry<String, Object> emap:newmap.entrySet()){
//			if("ID".equalsIgnoreCase(emap.getKey())||"OBJIP".equalsIgnoreCase(emap.getKey())||"CREATETIME".equalsIgnoreCase(emap.getKey()))
//				newmap.remove(emap.getKey());
//			else{
//				if(emap.getValue().getClass().getSimpleName().equalsIgnoreCase(TSurveyInfoObjtable.class.getSimpleName()))
//					newmap.put(emap.getKey(), emap.getValue());
//			}
//		}
		return newmap;
	}
}
